﻿using BCCommon;
using BCEntity.TMS.Agent.AgentBranchesConfig;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace BCData.TMS.Agent.AgentBranchesConfig
{
    public class AgentBranchesConfigData : IAgentBranchesConfigData
    {
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT AgentBranchesConfigId,AgentBranchesName,AgentRecordId,ContactPhone,
                                    ProvinceCode,ProvinceName,CityCode,CityName,CountyCode,CountyName,
                                    Longitude,Latitude,Address,IsSend,IsReceive,Remark,CreateTime,UpdateTime 
                                    FROM AgentBranchesConfig";

        public AgentBranchesConfigData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<AgentBranchesConfigEntity> Insert(AgentBranchesConfigEntity entity)
        {
            return await database.Master.InsertAsync(entity);
        }

        public async Task<AgentBranchesConfigEntity> GetById(long agentBranchesConfigId)
        {
            return await database.Slave.QueryEntityAsync<AgentBranchesConfigEntity>($"{QUERY} WHERE AgentBranchesConfigId = ?AgentBranchesConfigId", new { agentBranchesConfigId });
        }

        public async Task<AgentBranchesConfigEntity> Update(AgentBranchesConfigEntity entity)
        {
            return await database.Master.UpdateAsync(entity);
        }

        public async Task<int> Delete(long agentBranchesConfigId)
        {
            return await database.Master.ExecuteAsync("DELETE FROM AgentBranchesConfig WHERE AgentBranchesConfigId = ?AgentBranchesConfigId", new { agentBranchesConfigId });
        }

        public async Task<(IEnumerable<AgentBranchesConfigEntity>, int)> Query(long? agentRecordId, string agentBranchesName, 
            string contactPhone, string provinceCode, string cityCode, string countyCode, string address, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(agentBranchesName))
            {
                wheres.Add("AgentBranchesName LIKE ?AgentBranchesName");
                parameters.Add("?AgentBranchesName", $"%{agentBranchesName}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(contactPhone))
            {
                wheres.Add("ContactPhone LIKE ?ContactPhone");
                parameters.Add("?ContactPhone", $"%{contactPhone}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode = ?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode = ?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(address))
            {
                wheres.Add("Address LIKE ?Address");
                parameters.Add("?Address", $"%{address}%", DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} ORDER BY AgentBranchesConfigId DESC LIMIT {2},{3};
                            SELECT COUNT(1) FROM AgentBranchesConfig {1}",
                            QUERY,
                            wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                            (pageIndex - 1) * pageSize,
                            pageSize);
            return await database.Slave.QueryPageableListAsync<AgentBranchesConfigEntity>(sql, parameters);
        }

        public async Task<IEnumerable<AgentBranchesConfigEntity>> GetList(long? agentRecordId, string countyCode, string address)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(address))
            {
                wheres.Add("Address LIKE ?Address");
                parameters.Add("?Address", $"%{address}%", DbType.AnsiString);
            }
            string sql = string.Format("{0} {1} ORDER BY AgentBranchesConfigId DESC", QUERY, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await database.Slave.QueryAsync<AgentBranchesConfigEntity>(sql, parameters);
        }
    }
}
